import pymysql

def lj():
    try:
        conn = pymysql.connect(
            user="root",
            password="!QAZ2wsx",
            database="zyq",  # 确保数据库名正确
            host="192.168.112.128",
            port=3306,
            charset="utf8"
        )
        print("数据库连接成功")
        return conn
    except Exception as e:
        print(f"数据库连接失败: {e}")
        return None

def zx(conn,sql):
    try:
        # 通过链接对象可以获取游标
        cursor1 = conn.cursor()

        # 通过游标执行数据库命令
        # cursor1.execute("select * from user")  执行查询命令
        # 执行修改命令
        cursor1.execute(sql)
        conn.commit()

        # 查询结果
        rall = cursor1.fetchall() # 返回所有数据
        # r = cursor1.fetchone() # 返回其中一条数据
        print(rall)
        # print("数据库执行成功")
    except Exception:
        print("数据库执行失败")

def gb(conn):
    try:
        conn.close()
        # print("数据库关闭成功")
    except Exception:
        print("数据库关闭失败")

def cj():
    # 链接数据库
    conn = lj()
    sql = "create table if not exists employee(id int primary key auto_increment,name char(100),sex char(3),address char(200))"
    zx(conn, sql) # 执行命令
    gb(conn) # 关闭

def cr(sql):
    conn = lj()
    zx(conn, sql)
    gb(conn)

def sc(sql):
    conn = lj()
    zx(conn, sql)
    gb(conn)

def scsy(sql):
    conn = lj()
    zx(conn, sql)
    gb(conn)

def gg(sql):
    conn = lj()
    zx(conn, sql)
    gb(conn)

if __name__ == "__main__":
    # 获取数据库连接
    sjk = lj()

    # 执行查询
    # zx(sjk, "SELECT * FROM user")

    # 创建表格
    cj()

    # 增加数据
    import  faker

    for i in range(16,20):
        f = faker.Faker(locale="zh-CN")
        w = f.profile()
        print(w)
    # for i in range(12,16):
        sql = f"insert into employee values('{i}','{w.get("name")}','{w.get("sex")}','{w.get("address")}')"
        cr(sql)

    # 删除所有数据
    # sql = f"delete from employee"
    # scsy(sql)

    # 删除数据
    # sql = f"delete from employee where name = '田洋'"
    # sc(sql)

    # 修改数据
    # sql = f"update employee set sex = 'F' where name = '李宁'"
    # gg(sql)

    # 关闭链接
    gb(sjk)
